package com.apes.scm.masterdata.party.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.party.model.PartyRole;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;


public interface PartyRoleRepository extends CustomRepository<PartyRole, String> {
    @Query("select T from PartyRole T where 1 = 1 and T.id = :origin")
    PartyRole findPartyRole(@Param("origin") String origin);

    //@Query("select T from PartyRole T where T.id = ?1 and T.role.Id = ?2")
    PartyRole findByIdAndRoleId(String id, String roleId);

    //@Query("select T from PartyRole T where T.partyid = ?1 and T.role.Id = ?2")
    PartyRole findByPartyAndRole(long partyId, String roleId);

    PartyRole findByPartyIdAndRoleId(long partyId, String roleId);

    List<PartyRole> findByPartyId(long partyId);

    Page<PartyRole> findByRole(String id, String roleId, Pageable pageable);

    @Query(value = "select * from mas_party_role T where T.party_id = ?1 and T.role_Id = ?2", nativeQuery = true)
    PartyRole simpleFind(long partyId, String roleId);

    @Query(value = "SELECT T.ID, (SELECT ) FROM MAS_PARTY_ROLE T " +
            " WHERE T.ID IN (SELECT D.PERSON_ID " +
            "        FROM AUT_LOCAL_PERMIT_ITEM A, AUT_POSTS B, AUT_POST_USERS C, AUT_USERS D " +
            "       WHERE A.LOCAL_PERMIT_ID = B.LOCAL_PERMIT_ID AND B.ID = C.POST_ID AND C.USER_ID = D.ID " +
            "         AND A.TYPE = 'pt_dept' AND A.PURPOSE = 'pp_belong' AND A.ORG_ID = :deptId) ", nativeQuery = true)
    List<PartyRole> findPartyRoleByDept(@Param("deptId") String deptId);



    @Query(value = "select distinct t.*\n" +
            "  from mas_party_role t\n" +
            " where t.id in (select d.person_id\n" +
            "                  from aut_local_permit_item a,\n" +
            "                       aut_posts             b,\n" +
            "                       aut_post_users        c,\n" +
            "                       aut_users             d\n" +
            "                 where a.local_permit_id = b.local_permit_id\n" +
            "                   and b.id = c.post_id\n" +
            "                   and c.user_id = d.id\n" +
            "                   and a.type = 'pt_dept'\n" +
            "                   and a.purpose = 'pp_belong'\n" +
            "                   and a.org_id = :orgId)", nativeQuery = true)
    List<PartyRole> findAllSalesmanByDeptId(@Param("orgId") String orgId);

    @Query(value = " SELECT T.* FROM mas_party_role T WHERE t.role_id = 'Company'", nativeQuery = true)
    List<PartyRole> findAllCompany();

    @Query(value = "" +
            " SELECT T.* " +
            "  FROM MAS_PARTY_ROLE T " +
            " WHERE ROLE_ID = 'Supplier' " +
            "   AND PARTY_ID IN " +
            "       (SELECT PARTY_ID FROM MAS_PARTY_ROLE WHERE ROLE_ID = 'Company') ", nativeQuery = true)
    List<PartyRole> findAllInnerSupplier();

    @Query(value = "" +
            " SELECT T.* " +
            "  FROM MAS_PARTY_ROLE T " +
            " WHERE T.ID = ?1 AND ROLE_ID = 'Supplier' " +
            "   AND PARTY_ID IN " +
            "       (SELECT PARTY_ID FROM MAS_PARTY_ROLE WHERE ROLE_ID = 'Company') ", nativeQuery = true)
    PartyRole findInnerSupplier(String id);

    @Query("select T from PartyRole T JOIN T.party B WHERE B.partyType = :partyType and T.role.id = :role and T.lastUpdatedStamp > :times or (T.id > :id and T.lastUpdatedStamp = :times) order by T.lastUpdatedStamp, T.id asc ")
    List<PartyRole> findByLastUpdatedStampAndId(@Param("partyType") String partyType,@Param("role") String role, @Param("times") Date times, @Param("id") String id, Pageable pageable);

    @Query(value = " SELECT T FROM PartyRole T, Corporate b WHERE T.party.id = b.party.id and b.valid = 1 and T.role.id = 'Supplier' and (T.id like :id or T.party.name like %:name%)")
    Page<PartyRole> findValidSupplier(@Param("id")String id, @Param("name")String name, Pageable pageable);

    @Query(value = " SELECT T FROM PartyRole T, Corporate b WHERE T.party.id = b.party.id and b.valid = 1 and T.role.id = 'Supplier' and (T.id like :id or T.party.name like %:name%) and rownum = 1 ")
    PartyRole findValidSupplier(@Param("id")String id, @Param("name")String name);

    @Query(value = "SELECT T FROM PartyRole T WHERE (T.id like %:value% or T.party.name like %:value%) and T.role.id = 'Supplier' and T.party.partyType = 'corporate'" +
            " and T.party.id not in (select b.party.id from PartyRole b where b.role.id = 'Company') " +
            " and T.id != :noinId ")
    Page<PartyRole> findSupplier(@Param("value")String value, @Param("noinId") String noinId, Pageable pageable);

    @Query(value = "SELECT T FROM PartyRole T WHERE (T.id like %:value% or T.party.name like %:value%) and T.role.id = 'Supplier' and T.party.partyType = 'corporate'" +
            " and T.party.id not in (select b.party.id from PartyRole b where b.role.id = 'Company') " +
            " and T.id != :noinId ")
    Page<PartyRole> purchaseReturnQNoInnerSupplier(@Param("value")String value, @Param("noinId") String noinId, Pageable pageable);

    @Query(value = "SELECT T FROM PartyRole T WHERE T.role.id = 'Supplier' and T.party.partyType = 'corporate'" +
            " and T.party.id not in (select b.party.id from PartyRole b where b.role.id = 'Company') " +
            " and T.id != :noinId ")
    Page<PartyRole> purchaseReturnQNoInnerSupplier(@Param("noinId") String noinId, Pageable pageable);

    @Query(value = "SELECT T FROM PartyRole T WHERE T.role.id = 'Supplier' and T.party.partyType = 'corporate'" +
            " and T.party.id not in (select b.party.id from PartyRole b where b.role.id = 'Company') ")
    Page<PartyRole> findSupplierNoId_changePrice(Pageable pageable);

    @Query(value = "SELECT T FROM PartyRole T WHERE (T.id like %:value% or T.party.name like %:value%) and T.role.id = 'Supplier' and T.party.partyType = 'corporate'" +
            " and T.party.id not in (select b.party.id from PartyRole b where b.role.id = 'Company') ")
    Page<PartyRole> findSupplierInId_changePrice(@Param("value")String value, Pageable pageable);


    @Query("select new map(l.id as supplierId,l.lastUpdatedStamp as lastUpdatedStamp, l.createDate as createDate,  b.regProvince.name as regProvince, b.regProvince.id as regProvinceId," +
            "b.regCity.name as regCity, b.regCity.id as regCityId, b.regCounty.name as regCounty, " +
            "b.regCounty.id as regCountyId, NVL(d.description,' ') as supplierLevel,NVL(d.id,' ') as supplierLevelId, " +
            "b.regAddress as regAddress," +
            "b.valid as valid,b.insuranceProvider as insuranceProvider," +
            "a.name as name, a.id as partyId , e.insuranceCompany.id as insuranceCompanyId,NVL(e.insuranceCompany.abbreviation,' ') as abbreviation,e.APPCode as appCode)" +
            " from PartyRole l left join Party a on l.party.id = a.id left join Corporate b  on a.corporate.id = b.id " +
            " left join Enumeration d on b.supplierLevel.id = d.id" +
            " left join InsuranceCompanyDetail e on e.supplier.id = l.id" +
//            " left join InsuranceCompany c  on e.insuranceCompany.id = c.id  " +
            " where l.role.id = 'Supplier'  and (l.lastUpdatedStamp > :times) and e.APPCode is not null " +
            " order by l.lastUpdatedStamp, l.id asc ")

    List<Map<String, Object>> querySupplier(@Param("times") Date times, Pageable pageable);

    @Query(value = "select t.* from mas_party_role t where t.id in ( select T.company_id from s_access_company T where t.access_id = :accessId)", nativeQuery = true)
    List<PartyRole> findByAccessProvider(@Param("accessId")String accessId);


    @Query(value = " SELECT B.ID FNKHID, A.NAME FNKHMC, TO_CHAR(A.LAST_UPDATED_STAMP, 'YYYYMMDD.HH24MI.SS') TS01 " +
            "   FROM MAS_PARTY A, MAS_PARTY_ROLE B " +
            "  WHERE A.ID = B.PARTY_ID AND A.PARTY_TYPE = 'corporate' AND B.ROLE_ID = 'Customer' AND B.ID LIKE 'C%' " +
            "    AND TO_CHAR(A.LAST_UPDATED_STAMP, 'YYYYMMDD.HH24MI.SS') > :TS01 ", nativeQuery = true)
    List<Map> queryCustomer(@Param("TS01") String TS01);
    /**
     * fetch data by role id or name
     *
     * @param id role id
     * @param name party name
     * @param pageable pageable number
     * @return Page<PartyRole
     */
    @Query(value = " SELECT T FROM PartyRole T, Corporate b WHERE T.party.id = b.party.id and b.valid = 1 and T.role.id = 'Customer' and T.party.partyType='corporate' and (T.id like :id or T.party.name like %:name%)")
    Page<PartyRole> findCustomerByIdOrName(@Param("id")String id,@Param("name")String name, Pageable pageable);

    @Query(value = " SELECT * " +
            "  FROM MAS_PARTY_ROLE T " +
            " WHERE ROLE_ID = 'Supplier' " +
            "   AND(( PARTY_ID IN" +
            "       (SELECT PARTY_ID FROM MAS_PARTY_ROLE WHERE ROLE_ID = 'Company')) " +
            "       OR T.ID = 'A00000' OR T.ID = '004465' OR T.ID = 'G1007' " +
            "       )" +
            " and ((1 = :i) or ((t.id like %:value%) or (exists (select 1 from mas_party a where a.id = t.party_id and a.name like %:value%)) )) ",
            countQuery ="SELECT COUNT(*) " +
                    "  FROM MAS_PARTY_ROLE T " +
                    " WHERE ROLE_ID = 'Supplier' " +
                    "   AND(( PARTY_ID IN" +
                    "       (SELECT PARTY_ID FROM MAS_PARTY_ROLE WHERE ROLE_ID = 'Company')) " +
                    "       OR T.ID = 'A00000' OR T.ID = '004465' OR T.ID = 'G1007' " +
                    "       )" +
                    " and ((1 = :i) or ((t.id like %:value%) or (exists (select 1 from mas_party a where a.id = t.party_id and a.name like %:value%)) )) ",
            nativeQuery = true)
    Page<PartyRole> findByInternalOrOtherSupplier(Integer i, String value, Pageable pageable);

    @Query(value = " SELECT T FROM PartyRole T WHERE T.role.id = 'Employee'  and T.party.name like %:name% ")
    List<PartyRole> findEmployeeByName(@Param("name")String name);

//    @Query(value ="SELECT A.* FROM MAS_PARTY_ROLE A,MAS_STORE B,MAS_PARTY C WHERE A.ID = B.COMPANY_ID AND A.PARTY_ID = C.ID AND (B.CLOSING_DAY IS NULL OR TRUNC(B.CLOSING_DAY) > = TRUNC(SYSDATE)) AND A.ROLE_ID = 'Company' AND (A.ID LIKE %:id% OR C.NAME LIKE %:id%)",nativeQuery = true)
//    List<PartyRole> findAllByCompany(@Param("id") String id);


    @Query("SELECT DISTINCT(A) FROM PartyRole A,Store B WHERE A.id = B.company.id AND (B.closingDay IS NULL OR B.closingDay >= :date) AND A.role.id = 'Company' AND (A.id LIKE %:id% OR A.party.name LIKE %:id%)")
    List<PartyRole> findAllByCompany(@Param("id") String id,@Param("date") Date date);


//    @Query(value ="SELECT DISTINCT A.* FROM MAS_PARTY_ROLE A,MAS_STORE B,MAS_DEPT C,MAS_PARTY D WHERE A.ID = B.COMPANY_ID AND A.ID = C.COMPANY_ID AND A.PARTY_ID = D.ID AND A.ROLE_ID = 'Company' AND IF(:dept != '',(C.ID LIKE %:dept% OR C.NAME LIKE %:dept%),1=1) AND (A.ID LIKE %:company% OR D.NAME LIKE %:company%)",nativeQuery = true)
//    List<PartyRole> findAllByCompanyDeptState(@Param("company") String company,@Param("dept") String dept);

    @Query(value = "" +
            "SELECT ID FROM MAS_PARTY_ROLE A WHERE NOT EXISTS (SELECT 1 FROM MAS_PARTY_ROLE J WHERE J.PARTY_ID = A.PARTY_ID AND ROLE_ID = 'Warehousing') AND ROLE_ID = 'Company' ", nativeQuery = true)
    List<Map> noWarehousingRoleCompany();

    @Query(value = "SELECT ID FROM MAS_PARTY_ROLE A WHERE ROLE_ID = 'Company' ", nativeQuery = true)
    List<Map> allCompany();

    @Query("SELECT B FROM PartyRole B WHERE B.party.id = (SELECT A.party.id FROM PartyRole A WHERE A.id = :Id) " +
            "AND B.role.id = 'Supplier'")
    PartyRole findPartyBySupplier(@Param("Id")String Id);

    @Query("SELECT T FROM PartyRole T WHERE (T.id = :id OR T.party.name = :name) AND T.role.id = 'Employee'")
    PartyRole findByIdOrName(@Param("id") String id,@Param("name") String name);

    @Query(value = "SELECT A.* FROM MAS_PARTY_ROLE A,MAS_PARTY B WHERE A.ROLE_ID = 'Company' AND A.PARTY_ID = B.ID AND (A.ID in ('',:stringList) or A.ID LIKE %:key% OR B.NAME LIKE %:key% ) ",nativeQuery = true)
    List<PartyRole> findAllByValidAndKey(@Param("key") String key,List<String> stringList);


    @Query(value = "SELECT A.* FROM MAS_PARTY_ROLE A WHERE A.ROLE_ID = 'Company'",nativeQuery = true)
    List<PartyRole> findAllByValid();

    @Query(value = " SELECT * FROM MAS_PARTY_ROLE WHERE ROLE_ID = 'Supplier' ", nativeQuery = true)
    List<PartyRole> findSupplier();

    @Query("    select a\n" +
            "   from PartyRole a,PartyRole b\n" +
            "   where a.party.id = b.party.id\n" +
            "       and a.role.id = 'Company'\n" +
            "       and b.role.id = 'Operator'\n" +
            "       and (a.id like %:id% or a.party.name like %:name%)\n"
    )
    Page<PartyRole> findByOperator(@Param("id") String id, @Param("name") String name, Pageable pageable);

    @Query("    select a\n" +
            "   from PartyRole a,PartyRole b\n" +
            "   where a.party.id = b.party.id\n" +
            "       and a.role.id = 'Company'\n" +
            "       and b.role.id = 'Operator'\n" +
            "       and (a.id like %:id% or a.party.name like %:name%)\n" +
            "       and a.id in :companyIds"
    )
    Page<PartyRole> findByOperatorAndCompanyIds(@Param("id") String id, @Param("name") String name,@Param("companyIds") List<String> companyIds, Pageable pageable);


    @Query("SELECT A.id as id, A.party.name as name FROM PartyRole A WHERE A.id = :id")
    Map<String, Object> findIdAndName(@Param("id") String id);

    @Query(value = "SELECT A.ID FROM MAS_PARTY_ROLE A ,FN_CONTRACT B WHERE A.ID = B.COMPANY_ID AND B.STATE = 'approve' GROUP BY A.ID",nativeQuery = true)
    List<String> findAllByContract();

    @Query(value = "SELECT A.* FROM mas_party_role A,mas_party B where A.PARTY_ID = B.ID AND A.PARTY_ID " +
            " in (SELECT C.PARTY_ID FROM mas_corporate C where C.operation = '001073' and C.valid = 1) and A.role_id = 'Company' AND (A.ID LIKE %:key% OR B.NAME LIKE %:key%) " +
            " order by A.id desc",nativeQuery = true)
    List<PartyRole> queryOperationByFn(@Param("key") String key, Pageable pageable);

    @Query(value = "select a from PartyRole a where a.party.name = :name")
    PartyRole findByName(String name);
}
